# -*- coding: utf-8 -*-
"""
Created on Thu Feb 06 16:12:09 2025

@author: wangwenjie
"""

import numpy as np
import pandas as pd
import pymysql
import os
from sqlalchemy import create_engine

# 参数设置
str_date = '20250102'
end_date = '20250617'

# 提取数据
def get_BondData(): # 获取持仓和收益数据
     conf = {'username': 'hs_wangwenjie',
             'password': 'hs_wangwenjie#A0',
             'host': '192.168.201.187',
             'port': 3306,
             'db': 'touyan'}
     url = 'mysql+pymysql://%s:%s@%s:%s/%s' % (
     conf['username'], conf['password'], conf['host'], conf['port'], conf['db'])
     engine = create_engine(url)
     query = ("""select * 
                 from p_hs_all_fund_position_capital_structure_statistic where n_flag=1 
                 and t_date >= "%s" and c_capital_structure2 = '自主配置' and n_type_code = '30051'
                 and n_self_latest_asset_nav > 0""") % (str_date)
     df = pd.read_sql_query(query, engine)
     df = df[['c_fund_name',
                't_date',
                'c_capital_structure1',
                'c_capital_structure2',
                'n_total_asset_nav',
                'c_sub_fund_id',
                'c_sub_fund_name',
                'c_stype_name',
                'c_sub_org_name',
                'n_sub_asset_daily_return_ratio',
                'n_sub_asset_week_return_ratio',
                'n_sub_asset_month_return_ratio',
                'n_sub_asset_year_return_ratio',
                'n_self_daily_return',
                'n_self_week_return',
                'n_self_latest_asset_nav',
                ]]
     df.columns = ['母产品名称',
                    '估值日期',
                    '资金结构',
                    '资金分组',
                    '母产品资产净值',
                    '子产品ID',
                    '子产品名称',
                    '子产品的细分策略名称',
                    '子产品投顾名称',
                    '子产品当日收益率',
                    '子产品当周收益率',
                    '子产品当月收益率',
                    '子产品当年收益率',
                    '自营资金持仓当日收益',
                    '自营资金持仓本周收益',
                    '自营资金持仓当前规模',
                    ]
     return df

# 导出数据
def data_output(Port1, Port2):
    # 导出数据到excel
     path = os.getcwd()
     new_dir = os.path.join(path, 'output')
     if not os.path.exists(new_dir):
         os.makedirs(new_dir)
     wb = pd.ExcelWriter(os.path.join(new_dir, "自营数据跟踪.xlsx"))
     Port1.to_excel(wb, '自营持仓收益率', index=False)
     Port2.to_excel(wb, '自营持仓收益和规模', index=False)
     wb.save()
     return

# 处理数据
df = get_BondData()
df_ret1 = df[['估值日期','子产品ID','子产品名称','子产品的细分策略名称','子产品投顾名称','子产品当日收益率','子产品当周收益率','子产品当月收益率','子产品当年收益率']]
df_ret1 = df_ret1.drop_duplicates(subset=['估值日期','子产品ID'])
df_ret2 = df.groupby(['估值日期','子产品ID'])[['自营资金持仓当日收益','自营资金持仓本周收益']].sum()
df_ret2['weight'] = df.groupby(['估值日期','子产品ID'])['自营资金持仓当前规模'].sum()
df_ret2 = df_ret2.reset_index()
data_output(df_ret1, df_ret2)